set hive.exec.dynamic.partition = true;
set hive.exec.dynamic.partition.mode = 'nonstrict';
set hive.exec.max.dynamic.partitions = 200;
set hive.exec.max.dynamic.partitions.pernode = 200;
with order_incre as (--运单表的寄件网点和省市区的id,后期做判断
    select waybill_id as waybill_no
         , receiver_province_id   --收件省份id
         , receiver_city_id       --收件城市id
         , receiver_area_id       --收件区域id
         , real_pick_network_code --实际取件网点
    from jms_dwd.dwd_yl_oms_oms_order_dt
    where dt between date_add('{{ execution_date | cst_ds }}', -30) and '{{ execution_date | cst_ds }}'
      and waybill_id is not null
),
     abnormal_detail_waybill as (--剔除问题件和剔除退转件(发件扫描)的数据
         select waybill_no
         from jms_dwd.dwd_wide_abnormal_union_waybill_dt
         where dt between date_add('{{ execution_date | cst_ds }}', -30) and date_add('{{ execution_date | cst_ds }}', 0)
           and (scan_type = 'reback'
             or (scan_type = 'difficult'
                 and type_code in ('15', '15a')))
           and waybill_no is not null
         group by waybill_no
     ),
     package_list as
         (--
             select package_code   --一段码
                  , package_number --包号
             from (
                      select package_code   --一段码
                           , package_number --包号
                           , row_number() over (partition by package_number order by used_time desc ) as rn
                      from jms_dwd.dwd_tab_electronic_package_list_base_dt
                      where dt between date_add('{{ execution_date | cst_ds }}', -59) and '{{ execution_date | cst_ds }}'
                        and package_number is not null
                  ) a
             where rn = 1
         ),
     agent_point as (--门店关联code.用来判断入仓和出仓网点为空,这取这个门店对应的网点code
         select courier_station_code
              , network_code
              , network_name
              , business_id
         from jms_dim.dim_tab_agent_point_base
         where courier_station_code is not null
           and business_id is not null
         group by courier_station_code
                , network_code
                , network_name
                , business_id
     ),
     collect_base as (--门店关联code.用来判断入仓和出仓网点为空,这取这个门店对应的网点code
         select waybill_no
              , post_station_code --驿站编码_ef
              , scan_type
              , scan_site_code
              , real_oper_source_code
         from (
                  select waybill_no
                       , post_station_code --驿站编码_ef
                       , scan_type
                       , scan_site_code
                       , real_oper_source_code
                       , row_number() over (partition by waybill_no, scan_type order by scan_time ) as rn
                  from jms_dwd.dwd_tab_barscan_collect_base_dt
                  where dt between date_add('{{ execution_date | cst_ds }}', -4) and '{{ execution_date | cst_ds }}'
                    and scan_type in ('入库扫描', '出库扫描')
                    and scan_site_code is null
                    and waybill_no is not null
              ) a
         where rn = 1
     ),
     scan_delete as (--剔除删除的数据
         select billcode
              , scan_time
         from jms_ods.tab_barscan_scan_delete
         where dt between date_add('{{ execution_date | cst_ds }}', -4) and date_add('{{ execution_date | cst_ds }}', 0)
           and scan_type = '发件扫描'
           and billcode is not null
         group by billcode
                , scan_time
     ),

     first_second as (--通过一段码和二段获取到对应的末端网点
         select first_code
              , second_code
              , center_id
              , center_name
              , center_code
              , network_id
              , network_name
              , network_code
              , network_province_id
              , network_city_id
              , network_area_id
         from jms_dim.dim_first_second_third_code_info
         where code_type = 2
     ),
     second_code_table as (
         select second_code
              , center_id
              , center_name
              , center_code
              , network_id
              , network_name
              , network_code
              , network_province_id
              , network_city_id
              , network_area_id
         from jms_dim.dim_first_second_third_code_info
         where code_type = 2
         group by second_code
                , center_id
                , center_name
                , center_code
                , network_id
                , network_name
                , network_code
                , network_province_id
                , network_city_id
                , network_area_id
     ),
     network_distributi as (--分拨类型判断
         select network_id
              , id
              , name
              , code
              , distribution_type --1、可派货分拨 2、可交货分拨 3、可交货集散 4、可派货集散
         from jms_dim.dim_lmdm_sys_network_distributi
         where distribution_type = 4
         group by network_id
                , id
                , name
                , code
                , distribution_type --1、可派货分拨 2、可交货分拨 3、可交货集散 4、可派货集散
     ),
     network_whole as (
         select id
              , code
              , name
              , agent_code
              , agent_name
              , network_type --网点类型:1-总部,2-代理区,3-加盟商,4-中心,5-集散点,6-网点
         from jms_dim.dim_network_whole_massage
     ),
     branch_line as (
         select a.start_code --始发网点编号
              , a.start_name
              , a.end_code   --目的网点编号
              , a.end_name
              , a.id         --主键
              , a.cnt        --途径地数量
         from jms_dim.dim_yl_tms_branch_line_base_dt a
         where id is not null and dt ='{{ execution_date | cst_ds }}'
     ),
     branch_point as (
         select b.sort_num     --顺序编号
              , b.network_code --经停网点编号
              , b.network_name --经停网点
              , b.line_id
         from jms_dim.dim_yl_tms_branch_point_base b
         where line_id is not null
     ),
     same_branch_line as (--用来判断是否在一条支线上
         select a.start_code,
                a.start_name,
                b.network_code,
                b.network_name,
                a.end_code,
                a.end_name
         from branch_line a
                  join network_whole n
                       on n.code = a.start_code
                  left join branch_point b
                            on a.id = b.line_id
                                and b.sort_num = 2
         where a.cnt >= 3
           and n.network_type = 4
         group by a.start_code,
                  a.start_name,
                  b.network_code,
                  b.network_name,
                  a.end_code,
                  a.end_name
     ),
     --2入库扫描,出库扫描操作对应的网点如果为空,去扫描消息通知表拿门店关联对应的网点
     agent_point_collect_base as (
         select waybill_no                                  --运单
              , max(in_scansite_code)  as in_scansite_code  --入库网点
              , max(out_scansite_code) as out_scansite_code --出库网点
         from (
                  select a.waybill_no
                       , case
                             when scan_type = '入库扫描' and a.scan_site_code is null then b.network_code
                             else a.scan_site_code end as in_scansite_code
                       , case
                             when scan_type = '出库扫描' and a.scan_site_code is null then b.network_code
                             else a.scan_site_code end as out_scansite_code
                  from collect_base a
                           left join agent_point b
                                     on a.post_station_code = b.courier_station_code
                                         and a.real_oper_source_code = b.business_id
              ) a
         group by waybill_no
     ),
--3若宽表的入库和出库网点为空,取扫描通知表,
-- 关联订单表,拿到省市区id,
--剔除问题件,删除件
     trace_waybill_dis_order_collect as (
         select waybill_no                                                                                           --运单
              , first_code                                                                                           --一段码
              , second_code                                                                                          --二段码
              , third_code                                                                                           --三段码
              , real_pick_network_code                                                                               --寄件网点code
              , send_station_network_code                                                                            --发件网点
              , send_station_network_type                                                                            --'网点类型,1.加盟商,2.转运中心,3.集散点,4.普通网点,5.代理区,6.总部,0.其他'
              , send_station_send_later_time                                                                         --最晚发件时间
              , sent_station_send_packagecode                                                                        --发件所属包号
              , send_station_next_station                                                                            --发件下一站
              , send_station_next_statation_type                                                                     --类型
              , deliver_later_user                                                                                   --最晚派件员
              , instore_early_scantime                                                                               --最早入库时间
              , instore_early_scansitecode                                                                           --最早入库网点
              , in_scansite_code                                                                                     --collect中的入库网点
              , outstore_early_scantime                                                                              --最早出库时间
              , outstore_early_scansitecode                                                                          --最早出库网点
              , out_scansite_code                                                                                    --collect 中的出库网点
              , sign_early_scantime                                                                                  --最早签收时间
              , sign_early_scansitecode                                                                              --早早签收网点
              , next_next_network_code                                                                               --下下一站
              , next_next_code_type                                                                                  --下下一站类型
              , receiver_province_id                                                                                 --省id
              , receiver_city_id                                                                                     --市id
              , receiver_area_id                                                                                     --区域id
              , is_sign                                                                                              --是否时效签收,1是 0否
              , sign_time                                                                                            --时效签收时间
              , result_out_code                                                                                      --兜底出库
              , result_in_code                                                                                       --兜底入库
              , case
                    when sign_time = sign_early_scantime then sign_early_scansitecode
                    when sign_time = sign_early_scantime then result_in_code
                    when sign_time = outstore_early_scansitecode then result_out_code
                    else coalesce(sign_early_scansitecode, result_out_code, result_in_code) end as sign_network_code --时效签收网点
         from (
                  select waybill_next.waybill_no                                                            --运单
                       , waybill_next.first_code                                                            --一段码
                       , waybill_next.second_code                                                           --二段码
                       , waybill_next.third_code                                                            --三段码
                       , order_incre.real_pick_network_code                                                 --寄件网点code
                       , waybill_next.send_station_network_code                                             --发件网点
                       , waybill_next.send_station_network_type                                             --'网点类型,1.加盟商,2.转运中心,3.集散点,4.普通网点,5.代理区,6.总部,0.其他'
                       , waybill_next.send_station_send_later_time                                          --最晚发件时间
                       , waybill_next.sent_station_send_packagecode                                         --发件所属包号
                       , waybill_next.send_station_next_station                                             --发件下一站
                       , waybill_next.send_station_next_statation_type                                      --类型
                       , waybill_next.deliver_later_user                                                    --最晚派件员
                       , waybill_next.instore_early_scantime                                                --最早入库时间
                       , waybill_next.instore_early_scansitecode                                            --最早入库网点
                       , collect_base.in_scansite_code                                                      --collect中的入库网点
                       , waybill_next.outstore_early_scantime                                               --最早出库时间
                       , waybill_next.outstore_early_scansitecode                                           --最早出库网点
                       , collect_base.out_scansite_code                                                     --collect 中的出库网点
                       , waybill_next.sign_early_scantime                                                   --最早签收时间
                       , waybill_next.sign_early_scansitecode                                               --早早签收网点
                       , waybill_next.next_next_network_code                                                --下下一站
                       , waybill_next.next_next_code_type                                                   --下下一站类型
                       , order_incre.receiver_province_id                                                   --省id
                       , order_incre.receiver_city_id                                                       --市id
                       , order_incre.receiver_area_id                                                       --区域id
                       , case
                             when waybill_next.sign_early_scantime is not null or
                                  waybill_next.outstore_early_scantime is not null or
                                  waybill_next.instore_early_scantime is not null then 1
                             else 0 end                                                        as is_sign   --是否时效签收,1是 0否
                       , case
                             when waybill_next.sign_early_scantime is not null or
                                  waybill_next.outstore_early_scantime is not null or
                                  waybill_next.instore_early_scantime is not null
                                 then
                                 least(nvl(waybill_next.sign_early_scantime,
                                           from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss')),
                                       nvl(waybill_next.outstore_early_scantime,
                                           from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss')),
                                       nvl(waybill_next.instore_early_scantime,
                                           from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss')))
                             else null end                                                     as sign_time --签收时间
                       , coalesce(outstore_early_scansitecode, collect_base.out_scansite_code) as result_out_code
                       , coalesce(instore_early_scansitecode, collect_base.in_scansite_code)   as result_in_code
                  from (
                           select *
                           from jms_dm.dm_tab_missrate_trace_waybill_next_dt
                           where dt = '{{ execution_date | cst_ds }}'
                             and remark2 = 0 --0代表t1,t5未时效签收
                       ) waybill_next
                           left anti
                           join scan_delete scan_delete --剔除操作
                                on waybill_next.send_station_send_later_time = scan_delete.scan_time
                                    and waybill_next.waybill_no = scan_delete.billcode
                           left anti
                           join abnormal_detail_waybill abnormal_detail --剔除操作
                                on waybill_next.waybill_no = abnormal_detail.waybill_no
                           left join agent_point_collect_base collect_base --取扫描通知表的网点
                                     on waybill_next.waybill_no = collect_base.waybill_no
                           left join order_incre order_incre --拿到省市区id
                                     on waybill_next.waybill_no = order_incre.waybill_no
              ) a
     ),
--拿出一段二段码对应的的目的网点
     master_date as (
         select order_coll.waybill_no                                         as waybill_no                       --运单
              , order_coll.first_code                                         as first_code                       --一段码
              , order_coll.second_code                                        as second_code                      --二段码
              , order_coll.third_code                                         as third_code                       --三段码
              , order_coll.real_pick_network_code                             as real_pick_network_code           --寄件网点code
              , order_coll.send_station_network_code                          as send_station_network_code        --发件网点
              , net_who.name                                                  as send_station_network_name        --发件网点
              , net_who.agent_code                                            as send_station_agent_code
              , net_who.agent_name                                            as send_station_agent_name
              , order_coll.send_station_network_type                          as send_station_network_type        --'网点类型,1.加盟商,2.转运中心,3.集散点,4.普通网点,5.代理区,6.总部,0.其他'
              , order_coll.send_station_send_later_time                       as send_station_send_later_time     --最晚发件时间
              , order_coll.sent_station_send_packagecode                      as sent_station_send_packagecode    --发件所属包号
              , order_coll.send_station_next_station                          as send_station_next_station        --发件下一站
              , net_who3.name                                                 as send_station_next_station_name   --发件下一站
              , order_coll.send_station_next_statation_type                   as send_station_next_statation_type --类型
              , order_coll.deliver_later_user                                 as deliver_later_user               --最晚派件员
              , order_coll.next_next_network_code                             as next_next_network_code           --下下一站
              , order_coll.next_next_code_type                                as next_next_code_type              --下下一站类型
              , order_coll.receiver_province_id                               as receiver_province_id             --省id
              , order_coll.receiver_city_id                                   as receiver_city_id                 --市id
              , order_coll.receiver_area_id                                   as receiver_area_id                 --区域id
              , pac_list.package_code                                         as package_code                     --电子包的一段码
              , order_coll.is_sign                                            as is_sign                          --是否时效签收,1是 0否
              , order_coll.sign_time                                          as sign_time                        --签收时间
              , order_coll.sign_network_code                                  as sign_network_code                --时效签收网点
              , net_who2.id                                                   as sign_network_id                  --时效签收网点
              , net_who2.name                                                 as sign_network_name                --时效签收网点
              , nvl(first_second_1.network_id, first_second_2.network_id)     as network_id                       --通过段码表获得目的网点id
              , nvl(first_second_1.network_code, first_second_2.network_code) as network_code                     --通过段码表获得目的网点code
              , nvl(first_second_1.network_name, first_second_2.network_name) as network_name                     --通过段码表获得目的网点name
              , first_second_3.network_code                                   as sdmcf_code                       --关联不上为null,说明不一致,代表错分,错分件中运单号对应时效签收网点二段码与面单打印二段码对比，不一致的错分件
              , substr(order_coll.send_station_send_later_time, 1, 10)        as day_time
         from trace_waybill_dis_order_collect order_coll
                  left join package_list pac_list
                            on order_coll.sent_station_send_packagecode = pac_list.package_number
                  left join first_second first_second_1
                            on first_second_1.first_code = order_coll.first_code
                                and first_second_1.second_code = order_coll.second_code
                  left join second_code_table first_second_2
                            on first_second_2.second_code = order_coll.second_code
                                and first_second_2.network_province_id = order_coll.receiver_province_id
                                and first_second_2.network_city_id = order_coll.receiver_city_id
                                and first_second_2.network_area_id = order_coll.receiver_area_id
                  left join second_code_table first_second_3
                            on first_second_3.second_code = order_coll.second_code
                                and first_second_3.network_code = order_coll.sign_network_code
                  left join network_whole net_who
                            on net_who.code = order_coll.send_station_network_code
                  left join network_whole net_who2
                            on net_who2.code = order_coll.sign_network_code
                  left join network_whole net_who3
                            on net_who3.code = order_coll.send_station_next_station
     ),
     result_table as (
         select master.day_time
              , master.waybill_no                                              as billcode                      --运单
              , master.send_station_network_code                               as site_code                     --发件网点
              , master.send_station_network_name                               as site_name                     --发件网点
              , master.send_station_agent_code                                 as proxy_area_code
              , master.send_station_agent_name                                 as proxy_area_name
              , master.send_station_send_later_time                            as scantime                      --最晚发件时间
              , master.send_station_next_station                               as next_site_code                --发件下一站
              , master.send_station_next_station_name                          as next_site                     --发件下一站
              , master.deliver_later_user                                      as scanuser                      --最晚派件员
              , master.first_code                                              as first_code                    --一段码
              , master.second_code                                             as second_code                   --二段码
              , master.network_id                                              as secondcode_site_id            --通过二段码表获得目的网点id
              , master.network_code                                            as secondcode_site_code          --通过二段码表获得目的网点code
              , master.network_name                                            as secondcode_site_name          --通过二段码表获得目的网点name
              , net_dis3.code                                                  as distribution_code             --签收网点对应派件集散(字段展示)
              , net_dis3.name                                                  as distribution_name             --签收网点对应派件集散(字段展示)
              , net_dis4.code                                                  as out_enport_code               --二段码时效签收网点对应派件集散(字段展示)
              , net_dis4.name                                                  as out_enport_name               --二段码时效签收网点对应派件集散(字段展示)
              , master.sign_network_id                                         as sign_network_id               --时效签收网点
              , master.sign_network_code                                       as sign_network_code             --时效签收网点
              , master.sign_network_name                                       as sign_network_name             --时效签收网点
              , master.is_sign                                                 as is_sign                       --是否时效签收,1是 0否
              , case when bran_line_2.start_code is not null then 1 else 0 end as is_same_line1                 --时效签收是否同一支线
              , case when bran_line_1.start_code is not null then 1 else 0 end as is_same_line2                 --二段码网点是否同一支线
              , case
                    when (
                            master.is_sign = 1
                            and (
                                    (
                                            master.send_station_next_statation_type = 4
                                            and
                                            nvl(master.sign_network_code, 'test') <>
                                            nvl(master.send_station_next_station, 'test01')
                                            and bran_line_2.start_code is null
                                        )
                                    or (
                                            master.send_station_next_statation_type = 3
                                            and master.next_next_code_type = 4
                                            and net_dis2.code is null
                                        )
                                )
                        ) then 1
                    else 0 end                                                 as sign_cuofen                   --1代表签收错分
              , case
                    when (
                            master.is_sign = 0
                            and (
                                    (
                                         master.send_station_next_statation_type = 4
                                         and
                                         nvl(master.send_station_next_station, 'test') <>
                                         nvl(master.network_code, 'test01')
                                         and bran_line_1.start_code is null
                                     )
                            or (
                                         master.send_station_next_statation_type = 3
                                         and master.next_next_code_type = 4
                                         and net_dis1.code is null
                                     )
                                )
                        ) then 1
                    else 0 end                                                 as unsign_cuofen                 --1代表未签错分
              , master.sent_station_send_packagecode                           as sent_station_send_packagecode --包号
              , master.real_pick_network_code                                  as real_pick_network_code        --揽收网点
              , master.package_code                                            as package_code                  --包牌一段码
              , sdmcf_code
              , if(nvl(master.real_pick_network_code, 'test') = nvl(master.sign_network_code, 'test01'), 1,
                   0)                                                          as l_yes_ls_qs_flag              --1揽收网点=签收网点 0<>
              , if(master.sent_station_send_packagecode is null or trim(master.sent_station_send_packagecode) = '', 0, 1)     as l_pack_flag                   --1代表包号非空,0空
              , if(master.first_code = master.package_code, 1, 0)              as l_yes_first_pack_flag         --1代表一段码=包牌一段码,0<>
              , substr(master.send_station_send_later_time, 1, 10)             as dt
         from master_date master
                  left join network_distributi net_dis1 --type=2 下一站为集散时  二段码对应网点与发件集散是否有派件关系
                            on master.network_id = net_dis1.network_id
                                and master.send_station_next_station = net_dis1.code
                  left join network_distributi net_dis2 --type=1 下一站为集散时 签收网点与发件集散是否有派件关系
                            on master.sign_network_id = net_dis2.network_id
                                and master.send_station_next_station = net_dis2.code
                  left join network_distributi net_dis3 --签收网点对应派件集散(字段展示)
                            on master.sign_network_id = net_dis3.network_id
                  left join network_distributi net_dis4 --签收网点对应派件集散(字段展示)
                            on master.network_id = net_dis4.network_id
                  left join same_branch_line bran_line_1
                            on bran_line_1.start_code = master.send_station_network_code
                                and bran_line_1.network_code = master.send_station_next_station
                                and bran_line_1.end_code = master.network_code
                  left join same_branch_line bran_line_2
                            on bran_line_2.start_code = master.send_station_network_code
                                and bran_line_2.network_code = master.send_station_next_station
                                and bran_line_2.end_code = master.sign_network_code
     )

insert
overwrite
table
jms_dm.dm_tab_missrate_waybill_detail_dt
partition
(
dt
)
select day_time
     , billcode
     , site_code
     , site_name
     , proxy_area_code
     , proxy_area_name
     , scantime
     , next_site_code
     , next_site
     , scanuser
     , first_code
     , second_code
     , secondcode_site_id
     , secondcode_site_code
     , secondcode_site_name
     , distribution_code
     , distribution_name
     , out_enport_code
     , out_enport_name
     , sign_network_id
     , sign_network_code
     , sign_network_name
     , case
           when replace(second_code, '-', '') is null then 1
           when is_sign = 1 and sdmcf_code is null then 2
           else 3 end as errorseparate_type
     , is_sign
     , is_same_line1
     , is_same_line2
     , ypack_cuofen   as sign_cuofen
     , npack_cuofen   as unsign_cuofen
     , dt
from (
         select *
              , case
                    when l_pack_flag = 1 and l_yes_first_pack_flag = 1 and
                         (unsign_cuofen = 1 or sign_cuofen = 1) then 1
                    else 0 end as ypack_cuofen --1有包号错分
              , case
                    when l_pack_flag = 0 and (unsign_cuofen = 1 or sign_cuofen = 1) then 1
                    else 0 end as npack_cuofen --无包号错分
         from result_table
         where l_yes_ls_qs_flag = 0 --揽收<>签收
     ) a
where (ypack_cuofen = 1 or npack_cuofen = 1)
 distribute by dt;